<?php

namespace Keio\StudyBundle\Repository;

use Doctrine\ORM\EntityRepository;

class UserRepository extends EntityRepository
{
	//Tìm xem bài đang kiểm tra có record ở trong DB chưa.
	//CHO PHÉP SAI SỐ LÀ 3h, tức là 3h trở lại đây có record updated thì tiếp tục, ko thì return null (để tạo mới)

	public function getIdketquakiemtra($user_id, $baihoc_id, $date_time){
		//nếu $date_time = null thì lấy Timestamp cho date mới = 0
		if (is_null($date_time)){
			$dtStr = date("c", 0);
			//xảy ra khi muốn lấy hết records trong DB
		}else{
			$timeStamp = date_timestamp_get($date_time);
			//3h = 10800 seconds
			$dtStr = date("c", $timeStamp-10800);
		}
		$date_time_m3 = new \DateTime($dtStr);

		$em = $this->getEntityManager();
		$q = $em->createQuery(
          'SELECT k FROM KeioStudyBundle:Baihocketquakiemtra k ' .
          'WHERE k.user = (?1) ' .
          'AND k.baihocid = (?2) ' .
          'AND k.dateupdate > (?3) ' .
          'ORDER BY k.id DESC ' .
          '')
          ->setParameter(1, $user_id)
          ->setParameter(2, $baihoc_id)
          ->setParameter(3, $date_time_m3)
          ;
        $rsltsql = $q->getResult();
        return $rsltsql;
	}

	private function checkIfUsernameExist($username){
        $entity = $this->findByUsername($username);
        if (count($entity)==0){
        	return false;
        }return true;
    }
    //check các lỗi về username, email, pwd, $roles
    public function checkFieldErrorsOfUser($edit, $username, $email, $password, $repeatpwd, $roles){
        //check nếu pwd = pwdR
        $kq = array('ok'=>1, 'msg'=>'no error');
        if ($password != $repeatpwd){
        	$kq['ok']=0;
        	$kq['msg'] = 'Password and repeat password not match!';
        }elseif (!(filter_var($email, FILTER_VALIDATE_EMAIL))){
        	$kq['ok']=0;
        	$kq['msg'] = 'Email format incorrect';
        }elseif (($edit==0)&&($this->checkIfUsernameExist($username))){
        	$kq['ok']=0;
        	$kq['msg'] = 'Username exists';
        }elseif (strpos($username,' ')!=false){ //username có kí tự trắng
            $kq['ok']=0;
        	$kq['msg'] = 'Username cannot contain blank character';
        }elseif (count($roles)==0){
        	$kq['ok']=0;
        	$kq['msg'] = 'Role cannot be empty';
        }
        return $kq;
    }

    public function checkConstraintToDeleteUser($id){
		//check if used in some tables
		$em = $this->getEntityManager();
		//in packageuser
		$q = $em->createQuery(
          'SELECT b.id FROM KeioStudyBundle:Packageuser b ' .
          'LEFT JOIN b.user m ' .
          'WHERE m.id = (?1)' .
          '')
          ->setParameter(1, $id)
          ;
        $rsltsql = $q->getResult();
        if (count($rsltsql)>0){
        	return 1;
        }
        //in blog
        $q = $em->createQuery(
	      'SELECT p.id FROM KeioStudyBundle:Blog p ' .
	      'LEFT JOIN p.user m ' .
	      'WHERE m.id = (?1)' .
	      '')
	      ->setParameter(1, $id)
	     ;
	    $rsltsql = $q->getResult();
	    if (count($rsltsql)>0){
	     	return 1;
	    }
	    //in baihocketquakiemtra
        $q = $em->createQuery(
	      'SELECT p.id FROM KeioStudyBundle:Baihocketquakiemtra p ' .
	      'LEFT JOIN p.user m ' .
	      'WHERE m.id = (?1)' .
	      '')
	      ->setParameter(1, $id)
	     ;
	    $rsltsql = $q->getResult();
	    if (count($rsltsql)>0){
	     	return 1;
	    }

        return 0; //if passed all above
	}

}
?>